select    a14.ZoneNumber ZoneNumber,
    a12.Category06Code Category06Code,
    a11.OrderNumber OrderNumber,
    a11.LineNumber LineNumber,
    a11.HeaderBusinessUnit HeaderBusinessUnit,
    sum((Case when a17.Dates between DATEADD(DD, 0, '2010-09-06') and DATEADD(DD, 6, '2010-09-06') then a11.QuantityShipped else NULL end)) WJXBFS1,
    sum((Case when a17.Dates between DATEADD(DD, 0, '2010-09-06') and DATEADD(DD, 6, '2010-09-06') then a11.ExtendedPrice else NULL end)) WJXBFS2,
    sum((Case when a17.Dates between DATEADD(DD, 7, '2010-09-06') and DATEADD(DD, 13, '2010-09-06') then a11.QuantityShipped else NULL end)) WJXBFS3,
    sum((Case when a17.Dates between DATEADD(DD, 7, '2010-09-06') and DATEADD(DD, 13, '2010-09-06') then a11.ExtendedPrice else NULL end)) WJXBFS4
from    [RD_SalesMart].[dbo].Sales_Star_Facts    a11
    join    [RD_SalesMart].[dbo].Sales_Star_Customer    a12
     on     (a11.CustomerNumberID = a12.CustomerNumberID)
    join    [RD_SalesMart].[dbo].Sales_Star_Item    a13
     on     (a11.ItemNumberID = a13.ItemNumberID)
    join    [RD_SalesMart].[dbo].Sales_Star_FactsExt    a14
     on     (a11.FactsExtID = a14.FactsExtID)
    join    [RD_SalesMart].[dbo].Sales_Star_BatchDateID    a15
     on     (a11.FactsExtID = a15.Sales_FactsextID)
    join    [RD_SalesMart].[dbo].Sales_Star_BusinessUnit    a16
     on     (a11.BusinessUnitID = a16.BusinessUnitID)
    join    [RD_SalesMart].[dbo].Sales_Star_Dates    a17
     on     (a15.BATCHDATEID = a17.CompanyDateID)
where    (a11.OrderTypeCode in ('SO', 'CO', 'SU')
and a11.InvoiceDateID is not null
and a13.SalesCategory01Code not in ('MIS ', 'OTH ')
and a13.SalesCategory03Code not in ('PRT')
and a11.TransactionTypeID in (2)
and (a17.Dates between DATEADD(DD, 0, '2010-09-06') and DATEADD(DD, 6, '2010-09-06')
or a17.Dates between DATEADD(DD, 7, '2010-09-06') and DATEADD(DD, 13, '2010-09-06')))
and a16.BusinessUnitCode in (' 1', ' 001', ' 002', ' 003', ' 004', ' 005', ' 006')

group by    a14.ZoneNumber,
    a12.Category06Code,
    a11.OrderNumber,
    a11.LineNumber,
    a11.HeaderBusinessUnit 	 